-- Create triggers in the POSTGRESQL database

CREATE OR REPLACE FUNCTION check1 () RETURNS TRIGGER AS $$
DECLARE 
last1 VARCHAR;
last2 TIMESTAMP;
yesterday1 TIMESTAMP;
today1 TIMESTAMP;
hours1 REAL;
		
BEGIN
	SELECT in_or_out INTO last1 FROM hours WHERE employee_id = NEW.employee_id ORDER BY time DESC LIMIT 1; 
	IF last1 IS NULL THEN
    NEW.in_or_out = 'in';
  END IF;
  IF last1 = NEW.in_or_out THEN 
		IF last1 = 'in' THEN
			NEW.in_or_out := 'out';
    ELSEIF last1 = 'out' THEN 
			NEW.in_or_out := 'in';
    END IF;
  ELSEIF NEW.in_or_out NOT IN ('in', 'out') THEN 
		IF last1 = 'in' THEN 
			NEW.in_or_out := 'out';
    ELSEIF last1 = 'out' THEN 
			NEW.in_or_out := 'in';
    END IF;
	END IF;

  -- If the day has changed, close at midnight and add hours to the bank
	SELECT time INTO last2 FROM hours WHERE employee_id = NEW.employee_id ORDER BY time DESC LIMIT 1; 
	
  IF NEW.in_or_out = 'out' AND to_char(last2, 'YYYY-MM-DD') = to_char((NEW.time - interval '1 day'), 'YYYY-MM-DD') THEN
    
    SELECT to_char(last2, 'YYYY-MM-DD')||' 23:59:59' INTO yesterday1;
    SELECT to_char(NEW.time, 'YYYY-MM-DD')||' 00:00:00' INTO today1;
    SELECT (EXTRACT(EPOCH FROM NEW.time) - EXTRACT(EPOCH FROM today1))/3600 INTO hours1;
    INSERT INTO hour_bank (employee_id, hours, date) VALUES (NEW.employee_id, hours1, NEW.time);
    NEW.time := yesterday1;

  END IF;

	RETURN new;
END;
$$ LANGUAGE plpgsql;

drop trigger if exists check_in_or_out on hours;
create trigger check_in_or_out before insert on hours
for each row execute procedure check1();

CREATE OR REPLACE FUNCTION check2 () RETURNS TRIGGER AS $$
DECLARE 
expected REAL;
real1 REAL;
bank REAL;
diff REAL;
month1 integer;
year1 integer;

BEGIN
SELECT EXTRACT(YEAR FROM NEW.time)INTO year1 FROM hours;
SELECT EXTRACT(MONTH FROM NEW.time)INTO month1 FROM hours;

  IF NEW.in_or_out = 'out' THEN
    SELECT employee_hours(NEW.employee_id, month1, year1) INTO expected;
    SELECT worked_hours(NEW.employee_id, month1, year1) INTO real1;
    SELECT SUM(hours) INTO bank FROM hour_bank WHERE employee_id = NEW.employee_id;
    IF bank IS NULL THEN
      bank := 0;
    END IF;
    diff := real1 - expected - bank;
    IF diff > 0 THEN
      INSERT INTO hour_bank (employee_id, hours,date) VALUES (NEW.employee_id, diff, now());
      END IF;
  END IF;
	RETURN new;
END;

$$ LANGUAGE plpgsql;

drop trigger if exists check_hour_bank on hours;
CREATE TRIGGER check_hour_bank AFTER INSERT ON hours
FOR EACH ROW EXECUTE PROCEDURE check2();
